package org.hbrs.ooka.model.persistence;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class DatabaseSource {

	private static final String URL = "jdbc:mysql://localhost:3306/ookatest";
	private static final String USER = "ooka";
	private static final String PASSWORD = "ooka2012";

	private Connection m_connection = null;
	private Statement m_statement = null;

	public DatabaseSource() throws SQLException {
		DriverManager.registerDriver(new com.mysql.jdbc.Driver());

		Properties props = new Properties();
		props.put("user", USER);
		props.put("password", PASSWORD);

		this.m_connection = DriverManager.getConnection(URL, props);

		this.m_statement = this.m_connection.createStatement();
		this.initTables();
	}

	private void initTables() {
		ResultSet rs = null;

		try {
			rs = this.m_statement.executeQuery("select * from customer");
		} catch (SQLException e) {
			this.execute("create table customer(id INT NOT NULL, name VARCHAR(30) NOT NULL, vname VARCHAR(30) NOT NULL, ort VARCHAR(50) NOT NULL, PRIMARY KEY ( id ));");
			this.execute("create table product(id INT NOT NULL, bezeichnung VARCHAR(100) NOT NULL, PRIMARY KEY ( id ));");
			this.execute("create table customer_product(cid INT NOT NULL, pid INT NOT NULL, FOREIGN KEY (cid) REFERENCES customer(id) ON DELETE CASCADE, FOREIGN KEY (pid) REFERENCES product(id) ON DELETE CASCADE);");
		} finally {
			closeResultSet(rs);
		}
	}

	public ResultSet executeQuery(String query) {
		ResultSet rs = null;
		try {
			// System.out.println("DatabaseSource.executeQuery(), query: " +
			// query);
			rs = this.m_statement.executeQuery(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}

	public boolean execute(String query) {
		try {
			// System.out.println("DatabaseSource.execute(), query: " + query);
			return this.m_statement.execute(query);
		} catch (SQLException e) {
			return false;
		}
	}

	public void closeResultSet(ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void closeConnection() {
		if (this.m_connection != null) {
			try {
				this.m_connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}
